/****** Object:  StoredProcedure [dbo].[AuthZ_spIsInRole] ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AuthZ_spIsInRole]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[AuthZ_spIsInRole]
GO

/****** Object:  StoredProcedure [dbo].[AuthZ_spGetRolesByUser] ******/
SET ANSI_NULLS ON
GO

-- =============================================
-- Author:		<revans>
-- Create date: <7-7-2009>
-- Description:	<Determines if the Person is in the role.>
-- Param:
-- =============================================
CREATE PROCEDURE [dbo].[AuthZ_spIsInRole]

	@roleName as nvarchar(50)
,	@personID as int

AS
BEGIN
	BEGIN TRY
		SET NOCOUNT ON;
		
		CREATE TABLE [dbo].[#TopRoles] (RoleID int, ParentRoleID int)

		INSERT INTO [dbo].[#TopRoles]

		/*Get all immediate roles associated to the user. */
		SELECT role.RoleID
		, role.ParentRoleID
		FROM [dbo].[AuthZUser] auser
		JOIN [dbo].[AuthZUserRoleMapping] armap
		ON   armap.AuthZUserID = auser.AuthZUserID
		JOIN [dbo].Person p
		ON p.PersonnelNumber = auser.PersonnelNumber
		AND  p.PersonID = @personID
		JOIN [dbo].[Role] role
		ON   role.RoleID = armap.RoleID
		
		WHILE @@ROWCOUNT > 0
		BEGIN

			INSERT INTO [dbo].[#TopRoles]

			/*Get the children of all roles children. */
			SELECT child.RoleID
			, child.ParentRoleID
			FROM [dbo].[#TopRoles] parent
			JOIN [dbo].[Role] child
			ON   child.ParentRoleID = parent.RoleID  --Gets the children
			AND  not exists (           --Don't return rows that already exist.
			SELECT parent2.RoleID 
			FROM [dbo].[#TopRoles] parent2
			WHERE parent2.RoleID = child.RoleID)
		END

		IF EXISTS (SELECT *
		FROM [dbo].[#TopRoles] tr
		JOIN [dbo].[Role] r
		ON   r.RoleID = tr.RoleID
		AND r.RoleName = @roleName)
		BEGIN
			SELECT 'True'
		END
		ELSE
			SELECT 'False'

	END TRY
BEGIN CATCH
	DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;

    SELECT @ErrorMessage = ERROR_MESSAGE(),
           @ErrorSeverity = ERROR_SEVERITY(),
           @ErrorState = ERROR_STATE();
           
    RAISERROR (@ErrorMessage, -- Message text.
               @ErrorSeverity, -- Severity.
               @ErrorState -- State.
               );
END CATCH
END

GO
